from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import and_
from be.model import store
from be.model.store import Store_Book, invert_index,Book
from be.model.store import New_Order_Detail
from be.model.store import New_Order
from be.model.store import User
from be.model.timer import check_order_time, delete_unpaid_order, get_time_stamp, add_unpaid_order
from be.model.order_auto_cancel import Order

import uuid
import json
import logging
from be.model import db_conn
from be.model import error


class Buyer(db_conn.DBConn):
    def __init__(self):
        db_conn.DBConn.__init__(self)
        self.page_size = 3  # 默认页数设置为3

    def new_order(self, user_id: str, store_id: str, id_and_count: [(str, int)]) -> (int, str, str):
        order_id = ""
        try:
            if not self.user_id_exist(user_id):
                return error.error_non_exist_user_id(user_id) + (order_id,)
            if not self.store_id_exist(store_id):
                return error.error_non_exist_store_id(store_id) + (order_id,)
            uid = "{}_{}_{}".format(user_id, store_id, str(uuid.uuid1()))
            for book_id, count in id_and_count:
                row = self.session.query(store.Store_Book).filter(
                    and_(store.Store_Book.store_id == store_id, store.Store_Book.book_id == book_id)).first()
                # cursor = self.conn.execute(
                #     "SELECT book_id, stock_level, book_info FROM store "
                #     "WHERE store_id = ? AND book_id = ?;",
                #     (store_id, book_id))
                if row is None:
                    return error.error_non_exist_book_id(book_id) + (order_id,)
                stock_level = row.stock_level
                book_info = row.book_info

                book_info_json = json.loads(book_info)
                price = book_info_json.get("price")

                if stock_level < count:
                    return error.error_stock_level_low(book_id) + (order_id,)
                row = self.session.query(Store_Book).filter(
                    and_(Store_Book.store_id == store_id, Store_Book.book_id == book_id,
                         Store_Book.stock_level >= count)).update({'stock_level': Store_Book.stock_level - count})
                # cursor = self.conn.execute(
                #     "UPDATE store set stock_level = stock_level - ? "
                #     "WHERE store_id = ? and book_id = ? and stock_level >= ?; ",
                #     (count, store_id, book_id, count))

                # if cursor.rowcount == 0:
                if row == 0:
                    return error.error_stock_level_low(book_id) + (order_id,)

                new_order_detail_entity = New_Order_Detail(order_id=uid, book_id=book_id, count=count, price=price)
                row = self.session.add(new_order_detail_entity)
                # self.session.commit()
                # self.conn.execute(
                #         "INSERT INTO new_order_detail(order_id, book_id, count, price) "
                #         "VALUES(?, ?, ?, ?);",
                #         (uid, book_id, count, price))
            creat_time = get_time_stamp()
            new_order_entity = New_Order(order_id=uid, fk_store_id=store_id, fk_user_id=user_id, create_time=creat_time,
                                         status=0)
            self.session.add(new_order_entity)
            # self.conn.execute(
            #     "INSERT INTO new_order(order_id, store_id, user_id) "
            #     "VALUES(?, ?, ?);",
            #     (uid, store_id, user_id))
            # self.conn.commit()
            self.session.commit()
            order_id = uid
            add_unpaid_order(order_id)
        except SQLAlchemyError as e:
            logging.info("528, {}".format(str(e)))
            return 528, "{}".format(str(e)), ""
        except BaseException as e:
            logging.info("530, {}".format(str(e)))
            return 530, "{}".format(str(e)), ""

        return 200, "ok", order_id

    def payment(self, user_id: str, password: str, order_id: str) -> (int, str):
        # conn = self.conn
        session = self.session
        try:
            print("hello")
            # cursor = conn.execute("SELECT order_id, user_id, store_id FROM new_order WHERE order_id = ?", (order_id,))
            # row = cursor.fetchone()
            row = session.query(New_Order).filter(New_Order.order_id == order_id).first()
            if row is None:
                return error.error_invalid_order_id(order_id)
            order_id = row.order_id
            buyer_id = row.fk_user_id
            store_id = row.fk_store_id
            order_time = row.create_time

            if buyer_id != user_id:
                return error.error_authorization_fail()
            print("hello3")
            if check_order_time(order_time) == False:
                self.session.commit()
                delete_unpaid_order(order_id)
                o = Order()
                o.cancel_order(int(order_id))
                return error.error_invalid_order_id(order_id)
            print("hello4")
            row = session.query(store.User).filter(store.User.user_id == buyer_id).first()
            print("hello2")
            # cursor = conn.execute("SELECT balance, password FROM user WHERE user_id = ?;", (buyer_id,))
            # row = cursor.fetchone()
            if row is None:
                return error.error_non_exist_user_id(buyer_id)
            # balance = row[0]
            balance = row.balance
            pwd = row.password
            if password != pwd:
                return error.error_authorization_fail()

            row = session.query(store.User_Store).filter(store.User_Store.fk_store_id == store_id).first()
            # cursor = conn.execute("SELECT store_id, user_id FROM user_store WHERE store_id = ?;", (store_id,))
            # row = cursor.fetchone()
            if row is None:
                return error.error_non_exist_store_id(store_id)

            # seller_id = row[1]
            seller_id = row.fk_user_id

            if not self.user_id_exist(seller_id):
                return error.error_non_exist_user_id(seller_id)

            row = session.query(New_Order_Detail).filter(New_Order_Detail.order_id == order_id).all()
            # cursor = conn.execute("SELECT book_id, count, price FROM new_order_detail WHERE order_id = ?;", (order_id,))
            total_price = 0
            # for row in cursor:
            #     count = row[1]
            #     price = row[2]
            #     total_price = total_price + price * count
            for i in row:
                count = i.count
                price = i.price
                total_price = total_price + price * count
            print(balance)
            if balance < total_price:
                return error.error_not_sufficient_funds(order_id)
            row = session.query(User).filter(and_(User.balance >= total_price, User.user_id == buyer_id)).update(
                {'balance': User.balance - total_price})
            # cursor = conn.execute("UPDATE user set balance = balance - ?"
            #                       "WHERE user_id = ? AND balance >= ?",
            #                       (total_price, buyer_id, total_price))
            # if cursor.rowcount == 0:
            if row == 0:
                return error.error_not_sufficient_funds(order_id)
            row = session.query(User).filter(User.user_id == seller_id).update({'balance': User.balance + total_price})
            # cursor = conn.execute("UPDATE user set balance = balance + ?"
            #                       "WHERE user_id = ?",
            #                       (total_price, buyer_id))
            # if cursor.rowcount == 0:
            if row == 0:
                return error.error_non_exist_user_id(buyer_id)

            row = session.query(New_Order).filter(New_Order.order_id == order_id).update({'status': 1})
            # cursor = conn.execute("DELETE FROM new_order WHERE order_id = ?", (order_id, ))
            # if cursor.rowcount == 0:
            if row == 0:
                return error.error_invalid_order_id(order_id)

            # row = session.query(New_Order_Detail).filter(New_Order_Detail.order_id==order_id).delete()
            # cursor = conn.execute("DELETE FROM new_order_detail where order_id = ?", (order_id, ))
            # if cursor.rowcount == 0:
            if row == 0:
                return error.error_invalid_order_id(order_id)

            # conn.commit()
            session.commit()

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))

        except BaseException as e:
            return 530, "{}".format(str(e))

        return 200, "ok"

    def add_funds(self, user_id, password, add_value) -> (int, str):
        try:
            row = self.session.query(store.User).filter(store.User.user_id == user_id).first()
            # cursor = self.conn.execute("SELECT password  from user where user_id=?", (user_id,))
            # row = cursor.fetchone()
            if row is None:
                return error.error_authorization_fail()

            pwd = row.password
            if pwd != password:
                return error.error_authorization_fail()

            row = self.session.query(store.User).filter_by(user_id=user_id).update(
                {'balance': store.User.balance + add_value, 'user_id': user_id})
            # cursor = self.conn.execute(
            #     "UPDATE user SET balance = balance + ? WHERE user_id = ?",
            #     (add_value, user_id))
            if row == 0:
                return error.error_non_exist_user_id(user_id)

            self.session.commit()
            # self.conn.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))

        return 200, "ok"

    def take_over(self, user_id, order_id):
        session = self.session
        try:
            if not self.user_id_exist(user_id):
                return error.error_non_exist_user_id(user_id)

            row = session.query(New_Order).filter(
                and_(New_Order.order_id == order_id, New_Order.fk_user_id == user_id)).first()
            if row is None:
                return error.error_invalid_order_id(order_id)
            if row.status != 2:
                return error.error_invalid_order_id(order_id)

            row = session.query(New_Order).filter(
                and_(New_Order.order_id == order_id, New_Order.fk_user_id == user_id)).update({'status': 3})

            if row == 0:
                return error.error_invalid_order_id(order_id)

            session.commit()

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
        return 200, "ok"

    def order_cancel(self, user_id, order_id):
        session = self.session
        try:
            if not self.user_id_exist(user_id):
                return error.error_non_exist_user_id(user_id)

            row = session.query(New_Order).filter(
                and_(New_Order.order_id == order_id, New_Order.fk_user_id == user_id)).first()
            if row is None:
                return error.error_invalid_order_id(order_id)
            if row.status != 0:
                return error.error_invalid_order_id(order_id)

            row = session.query(New_Order).filter(
                and_(New_Order.order_id == order_id, New_Order.fk_user_id == user_id)).update({'status': -1})

            if row == 0:
                return error.error_invalid_order_id(order_id)

            session.commit()

        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
        return 200, "ok"

    def history_order(self, user_id):
        session = self.session
        try:
            if not self.user_id_exist(user_id):
                return error.error_non_exist_user_id(user_id)
            row = session.query(New_Order).filter(New_Order.fk_user_id == user_id).first()
            if row is None:
                return error.error_non_exist_user_id(user_id)
            row = session.query(New_Order).filter(New_Order.fk_user_id == user_id).order_by(
                New_Order.create_time.desc()).all()
            order_ids = []
            for i in row:
                order_ids.append(i.order_id)
            session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e))
        except BaseException as e:
            return 530, "{}".format(str(e))
        return 200, "ok", order_ids

    def search_tags(self, search_key, page=0) -> (int, str, list):
        ret=[]
        try:
            session = self.session
            page_size = self.page_size
            if page > 0:
                page_lower = self.page_size * (page - 1)
                row = session.query(invert_index).filter(invert_index.search_key == search_key).order_by(
                    invert_index.search_id.desc()).limit(page_size).offset(page_lower).all()
            else:
                row = session.query(invert_index).filter(invert_index.search_key == search_key).order_by(
                    invert_index.search_id.desc()).all()
            if len(row) != 0:
                for i in row:
                    row_tmp = self.session.query(Book).filter(Book.book_id == i.search_id).first()
                    if row_tmp is None:
                        continue
                    else:
                        title = row_tmp.title
                        author_ = row_tmp.author
                        publisher = row_tmp.publisher
                        book_intro = row_tmp.book_intro
                        tags = row_tmp.tags
                        ret.append(
                            {'title': title, 'author': author_, 'publisher': publisher,
                             'book_intro': book_intro,
                             'tags': tags})
            self.session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e)), []
        except BaseException as e:
            return 530, "{}".format(str(e)), []
        return 200, "ok", ret

    def search_title(self, search_title, page=0) -> (int, str, list):
        ret=[]
        try:
            session = self.session
            page_size = self.page_size
            if page > 0:
                page_lower = self.page_size * (page - 1)
                row = session.query(invert_index).filter(invert_index.book_title == search_title).order_by(
                    invert_index.search_id.desc()).limit(page_size).offset(page_lower).all()
            else:
                row = session.query(invert_index).filter(invert_index.book_title == search_title).order_by(
                    invert_index.search_id.desc()).all()
            if len(row) != 0:
                for i in row:
                    row_tmp = self.session.query(Book).filter(Book.book_id == i.search_id).first()
                    if row_tmp is None:
                        continue
                    else:
                        title = row_tmp.title
                        author_ = row_tmp.author
                        publisher = row_tmp.publisher
                        book_intro = row_tmp.book_intro
                        tags = row_tmp.tags
                        ret.append(
                            {'title': title, 'author': author_, 'publisher': publisher,
                             'book_intro': book_intro,
                             'tags': tags})
            self.session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e)), []
        except BaseException as e:
            return 530, "{}".format(str(e)), []
        return 200, "ok", ret

    def search_author(self, author, page=0) -> (int, str, list):
        ret = []
        try:
            session = self.session
            page_size = self.page_size
            if page > 0:
                page_lower = self.page_size * (page - 1)
                row = session.query(invert_index).filter(invert_index.book_author == author).order_by(
                    invert_index.search_id.desc()).limit(page_size).offset(page_lower).all()
            else:
                row = session.query(invert_index).filter(invert_index.book_author == author).order_by(
                    invert_index.search_id.desc()).all()
            if len(row) != 0:
                for i in row:
                    row_tmp = self.session.query(Book).filter(Book.book_id == i.search_id).first()
                    if row_tmp is None:
                        continue
                    else:
                        title = row_tmp.title
                        author_ = row_tmp.author
                        publisher = row_tmp.publisher
                        book_intro = row_tmp.book_intro
                        tags = row_tmp.tags
                        ret.append(
                            {'title': title, 'author': author_, 'publisher': publisher,
                             'book_intro': book_intro,
                             'tags': tags})
            self.session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e)), []
        except BaseException as e:
            return 530, "{}".format(str(e)), []
        return 200, "ok", ret

    def instore_search_tags(self, search_key, store_id,page=0) -> (int, str, list):
        ret = []
        try:
            session = self.session
            page_size = self.page_size
            if page > 0:
                page_lower = self.page_size * (page - 1)
                row = session.query(invert_index).filter(invert_index.search_key == search_key,invert_index.store_id == store_id).order_by(
                    invert_index.search_id.desc()).limit(page_size).offset(page_lower).all()
            else:
                row = session.query(invert_index).filter(invert_index.search_key == search_key,invert_index.store_id ==store_id).order_by(
                    invert_index.search_id.desc()).all()
            if len(row) != 0:
                for i in row:
                    row_tmp = self.session.query(Book).filter(Book.book_id == i.search_id).first()
                    if row_tmp is None:
                        continue
                    else:
                        title = row_tmp.title
                        author_ = row_tmp.author
                        publisher = row_tmp.publisher
                        book_intro = row_tmp.book_intro
                        tags = row_tmp.tags
                        ret.append(
                            {'title': title, 'author': author_, 'publisher': publisher,
                             'book_intro': book_intro,
                             'tags': tags})
            self.session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e)), []
        except BaseException as e:
            return 530, "{}".format(str(e)), []
        return 200, "ok", ret

    def instore_search_author(self, author, store_id, page=0) -> (int, str, list):
        ret = []
        try:
            session = self.session
            page_size = self.page_size
            if page > 0:
                page_lower = self.page_size * (page - 1)
                row = session.query(invert_index).filter(invert_index.book_author == author,invert_index.store_id == store_id).order_by(
                    invert_index.search_id.desc()).limit(page_size).offset(page_lower).all()
            else:
                row = session.query(invert_index).filter(invert_index.book_author == author,invert_index.store_id == store_id).order_by(
                    invert_index.search_id.desc()).all()
            if len(row) != 0:
                for i in row:
                    row_tmp = self.session.query(Book).filter(Book.book_id == i.search_id).first()
                    if row_tmp is None:
                        continue
                    else:
                        title = row_tmp.title
                        author_ = row_tmp.author
                        publisher = row_tmp.publisher
                        book_intro = row_tmp.book_intro
                        tags = row_tmp.tags
                        ret.append(
                            {'title': title, 'author': author_, 'publisher': publisher,
                             'book_intro': book_intro,
                             'tags': tags})
            self.session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e)), []
        except BaseException as e:
            return 530, "{}".format(str(e)), []
        return 200, "ok", ret

    def instore_search_title(self, search_title, store_id, page=0) -> (int, str, list):
        ret = []
        try:
            session = self.session
            page_size = self.page_size
            if page > 0:
                page_lower = self.page_size * (page - 1)
                row = session.query(invert_index).filter(invert_index.book_title == search_title,invert_index.store_id == store_id).order_by(
                    invert_index.search_id.desc()).limit(page_size).offset(page_lower).all()
            else:
                row = session.query(invert_index).filter(invert_index.book_title == search_title,invert_index.store_id == store_id).order_by(
                    invert_index.search_id.desc()).all()
            if len(row) != 0:
                for i in row:
                    row_tmp = self.session.query(Book).filter(Book.book_id == i.search_id).first()
                    if row_tmp is None:
                        continue
                    else:
                        title = row_tmp.title
                        author_ = row_tmp.author
                        publisher = row_tmp.publisher
                        book_intro = row_tmp.book_intro
                        tags = row_tmp.tags
                        ret.append(
                            {'title': title, 'author': author_, 'publisher': publisher,
                             'book_intro': book_intro,
                             'tags': tags})
            self.session.commit()
        except SQLAlchemyError as e:
            return 528, "{}".format(str(e)), []
        except BaseException as e:
            return 530, "{}".format(str(e)), []
        return 200, "ok", ret